package cn.edu.gench.learn.dao;

import cn.edu.gench.learn.model.City;
import cn.edu.gench.learn.model.District;
import cn.edu.gench.learn.model.Province;
import cn.edu.gench.learn.util.ConnectFactory;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/5/17.
 */
@Service
public class CityDao{


    public List<Province> getAllProvice() {

        List<Province> provinces = new ArrayList<Province>();
        try {
            // 加载驱动程序
            Connection conn = ConnectFactory.getConnection();
            Statement statement = conn.createStatement();
            // 要执行的SQL语句
            String sql = "select * from d15_province" ;
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                Province province = new Province();
                province.setName(rs.getString("name"));
                province.setId(rs.getInt("id"));
                provinces.add(province);
            }
            ConnectFactory.close(conn,rs,statement);
        } catch(SQLException e) {
            e.printStackTrace();
        }
        return provinces;

    }

    public List<City> getCityByPid(int pid) {
        List<City> cities = new ArrayList<City>();
        try {
            // 加载驱动程序
            Connection conn = ConnectFactory.getConnection();
            Statement statement = conn.createStatement();
            // 要执行的SQL语句
            String sql = "select * from d15_city where province_id = " + pid;
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                City city = new City();
                city.setName(rs.getString("name"));
                city.setId(rs.getInt("id"));
                city.setProvinceId(rs.getInt("province_id"));
                cities.add(city);
            }
            ConnectFactory.close(conn,rs,statement);
        } catch(SQLException e) {
            e.printStackTrace();
        }
        return cities;
    }


    public List<District> getDistrictByCid(int cid) {
        List<District> districts = new ArrayList<District>();
        try {
            // 加载驱动程序
            Connection conn = ConnectFactory.getConnection();
            Statement statement = conn.createStatement();
            // 要执行的SQL语句
            String sql = "select * from d15_district where city_id = " + cid;
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                District district = new District();
                district.setName(rs.getString("name"));
                district.setId(rs.getInt("id"));
                district.setCityId(rs.getInt("city_id"));
                districts.add(district);
            }
            ConnectFactory.close(conn,rs,statement);
        } catch(SQLException e) {
            e.printStackTrace();
        }
        return districts;
    }



}
